package com.basic.datapersist.dbadapter;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;

import com.basic.common.FuncStatic;
import com.basic.datapersist.DbConnectTool;

public abstract class MysqlDb implements DbConnectTool {
	private static Logger logger = Logger.getLogger(MysqlDb.class);
	protected BasicDataSource dataSource = null;

	private String getConnectUrl() {
		String cons = "jdbc:mysql://"
				+ getDbIpAndPort()
				+ "/"
				+ getDbName()
				+ "?autoReconnect=true&failOverReadOnly=false&characterEncoding="+getEncode();
		logger.info("local db :" + cons);
		return cons;
	}
	protected abstract String getDbIpAndPort();
	protected abstract String getDbName();
	protected abstract String getLoginName();
	protected abstract String getPassword();
	protected abstract String getEncode();
	 
	protected MysqlDb(){
		initDataSource();
	}
	protected void initDataSource(){
		try {
			dataSource = new BasicDataSource(); 
			dataSource.setValidationQuery("select 1");
			dataSource.setDriverClassName("com.mysql.jdbc.Driver");
			dataSource.setUrl(getConnectUrl());
			dataSource.setUsername(getLoginName());
			dataSource.setPassword(getPassword());
			dataSource.setDefaultAutoCommit(false);
		} catch (Exception e) {
			logger.error("数据库初始化错误",e);
		}
 	}
	@Override
	public Connection getConnection() {
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			logger.error(FuncStatic.errorTrace(e));
		}
		return null;
	}
  
	@Override
	public String formatPagerSql(String sql, int startIndex, int length) {
		return sql + " limit " + startIndex + ","+length;
	}

	public List<String> allTablesName()  {
		List<String> list = new ArrayList<String>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet resultSet = null;
		try {
			conn = this.getConnection();
			stmt = conn.createStatement();
			resultSet = stmt.executeQuery("show tables");
			while (resultSet.next()){
				list.add(resultSet.getString(1));
			}
		} catch (Exception e) {
			logger.error(FuncStatic.errorTrace(e));
		}finally{
			try {
				if(resultSet != null)
					resultSet.close();
			} catch (Exception e2) {
				logger.error(FuncStatic.errorTrace(e2));
			}
			try {
				if(stmt != null)
					stmt.close();
			} catch (Exception e2) {
				logger.error(FuncStatic.errorTrace(e2));
			}
			try {
				if(conn != null)
					conn.close();
			} catch (Exception e2) {
				logger.error(FuncStatic.errorTrace(e2));
			}
		}
		return list;
	}
	
	/**
	 * 返回某个表的所有字段及字段类型，字段与字段类型之间用逗号分隔
	 * @param table
	 * @return
	 */
	public List<String> allFields(String table){
		List<String> list = new ArrayList<String>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet resultSet = null;
		try {
			conn = this.getConnection();
			stmt = conn.createStatement();
			resultSet = stmt.executeQuery("SHOW FULL COLUMNS FROM "+table);
			while (resultSet.next()){
				list.add(resultSet.getString(1)+","+resultSet.getString(2));
			}
		} catch (Exception e) {
			logger.error(FuncStatic.errorTrace(e));
		}finally{
			try {
				if(resultSet != null)
					resultSet.close();
			} catch (Exception e2) {
				logger.error(FuncStatic.errorTrace(e2));
			}
			try {
				if(stmt != null)
					stmt.close();
			} catch (Exception e2) {
				logger.error(FuncStatic.errorTrace(e2));
			}
			try {
				if(conn != null)
					conn.close();
			} catch (Exception e2) {
				logger.error(FuncStatic.errorTrace(e2));
			}
		}
		return list;
	}
	 
	/**
	 * 多个主键用逗号隔开
	 * @param table
	 * @return
	 */
	public String getKeyFieldName(String table)
	{ 
		String key = new String();
		Connection conn = null;
		Statement stmt = null;
		ResultSet resultSet = null;
		try {
			conn = this.getConnection();
			stmt = conn.createStatement();
			String sql = "SELECT t.TABLE_NAME, c.COLUMN_NAME "+
		"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t, INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c "+
		"WHERE t.TABLE_NAME = c.TABLE_NAME "+
		"AND t.TABLE_NAME =  '{0}' "+
		"AND t.CONSTRAINT_TYPE =  'PRIMARY KEY'";
			resultSet = stmt.executeQuery(FuncStatic.format(sql, table));
			while(resultSet.next()){
				key = resultSet.getString(2) + ",";
			}
			key = key.substring(0,key.length()-1);
		} catch (Exception e) {
			logger.error(FuncStatic.errorTrace(e));
		}finally{
			try {
				if(resultSet != null)
					resultSet.close();
			} catch (Exception e2) {
				logger.error(FuncStatic.errorTrace(e2));
			}
			try {
				if(stmt != null)
					stmt.close();
			} catch (Exception e2) {
				logger.error(FuncStatic.errorTrace(e2));
			}
			try {
				if(conn != null)
					conn.close();
			} catch (Exception e2) {
				logger.error(FuncStatic.errorTrace(e2));
			}
		}
		return key;
	}
}
